/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package jprojetopharmalink;

import java.awt.TextArea;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Scanner;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.DefaultComboBoxModel;
import javax.swing.DefaultListModel;
import javax.swing.JFileChooser;
import javax.swing.JFrame;
import javax.swing.JOptionPane;
import javax.swing.JProgressBar;
import javax.swing.ListModel;
import javax.swing.SwingUtilities;
import javax.swing.UIManager;
import javax.swing.UnsupportedLookAndFeelException;
import javax.swing.filechooser.FileNameExtensionFilter;
import javax.swing.table.DefaultTableModel;
import static jprojetopharmalink.Update.con;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 *
 * @author Rodrigo
 */
public class Principal extends javax.swing.JFrame {

    File[] arquivo = null;
    String caminhoPlaninhaDefaut = null;
    String caminhoRetornoDefaut = null;
    String caminhoEnvioDefaut = null;
    String caminhoArquivo = null;
    public String idSelecionado = null;
    public static Connection con;
    String modo;
    String idDistribuidora;
    String idDistrRet;
    String[] arrayCnpj;
    String[] arrayIdDistr;
    String cnpjPararetorno;
    String pedidoPararetorno;

    /**
     * Creates new form Principal
     */
    public Principal() {
        initComponents();
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jButton1 = new javax.swing.JButton();
        jTabbedPane1 = new javax.swing.JTabbedPane();
        jPanel1 = new javax.swing.JPanel();
        jLabel2 = new javax.swing.JLabel();
        jLabel1 = new javax.swing.JLabel();
        jProgressBar1 = new javax.swing.JProgressBar();
        jProgressBar2 = new javax.swing.JProgressBar();
        jPanel8 = new javax.swing.JPanel();
        jScrollPane2 = new javax.swing.JScrollPane();
        jTextArea2 = new javax.swing.JTextArea();
        jPanel9 = new javax.swing.JPanel();
        jTextField1 = new javax.swing.JTextField();
        jButton2 = new javax.swing.JButton();
        jButton3 = new javax.swing.JButton();
        jComboBoxProjeto = new javax.swing.JComboBox();
        jLabel3 = new javax.swing.JLabel();
        jLabel4 = new javax.swing.JLabel();
        jComboBoxDistribuidora = new javax.swing.JComboBox();
        jLabelCnpj = new javax.swing.JLabel();
        jPanel10 = new javax.swing.JPanel();
        jScrollPane1 = new javax.swing.JScrollPane();
        jTextArea1 = new javax.swing.JTextArea();
        jPanel3 = new javax.swing.JPanel();
        jScrollPane6 = new javax.swing.JScrollPane();
        jTextArea4 = new javax.swing.JTextArea();
        jPanel11 = new javax.swing.JPanel();
        jScrollPane5 = new javax.swing.JScrollPane();
        jList1 = new javax.swing.JList();
        jLabel10 = new javax.swing.JLabel();
        jButtonFormRetornoAdicionar = new javax.swing.JButton();
        jButton11 = new javax.swing.JButton();
        jScrollPane4 = new javax.swing.JScrollPane();
        jList2 = new javax.swing.JList();
        jLabel15 = new javax.swing.JLabel();
        jPanel12 = new javax.swing.JPanel();
        jLabel13 = new javax.swing.JLabel();
        jTextFieldDistrOrigem = new javax.swing.JTextField();
        jLabel14 = new javax.swing.JLabel();
        jComboBoxDistrRetorno = new javax.swing.JComboBox();
        jButton10 = new javax.swing.JButton();
        jPanel2 = new javax.swing.JPanel();
        jLabel11 = new javax.swing.JLabel();
        jPanel6 = new javax.swing.JPanel();
        jButton7 = new javax.swing.JButton();
        jLabel7 = new javax.swing.JLabel();
        jTextFieldCaminhoRetorno = new javax.swing.JTextField();
        jButton6 = new javax.swing.JButton();
        jButton5 = new javax.swing.JButton();
        jTextFieldCaminhoEnvio = new javax.swing.JTextField();
        jLabel6 = new javax.swing.JLabel();
        jLabel5 = new javax.swing.JLabel();
        jTextFieldCaminhoPlanilha = new javax.swing.JTextField();
        jButton4 = new javax.swing.JButton();
        jPanel4 = new javax.swing.JPanel();
        jScrollPane3 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();
        jPanel5 = new javax.swing.JPanel();
        jTextFieldDescricao = new javax.swing.JTextField();
        jLabel8 = new javax.swing.JLabel();
        jTextFieldCnpj = new javax.swing.JTextField();
        jLabel9 = new javax.swing.JLabel();
        jButton8 = new javax.swing.JButton();
        jButton9 = new javax.swing.JButton();
        jPanel7 = new javax.swing.JPanel();
        jButtonExcluir = new javax.swing.JButton();
        jButtonEditar = new javax.swing.JButton();
        jButtonBuscar = new javax.swing.JButton();
        jTextFieldBusca = new javax.swing.JTextField();
        jLabel12 = new javax.swing.JLabel();
        jMenuBar1 = new javax.swing.JMenuBar();

        jButton1.setText("jButton1");

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);
        setTitle("Converte Excel em Pedido (RedeFarmes)");
        setName("Tela Principal"); // NOI18N

        jTabbedPane1.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jTabbedPane1MouseClicked(evt);
            }
        });
        jTabbedPane1.addChangeListener(new javax.swing.event.ChangeListener() {
            public void stateChanged(javax.swing.event.ChangeEvent evt) {
                jTabbedPane1StateChanged(evt);
            }
        });
        jTabbedPane1.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                jTabbedPane1FocusGained(evt);
            }
        });
        jTabbedPane1.addKeyListener(new java.awt.event.KeyAdapter() {
            public void keyPressed(java.awt.event.KeyEvent evt) {
                jTabbedPane1KeyPressed(evt);
            }
        });

        jLabel2.setText("Contato: rodrigo.massayoshi@yahoo.com.br");

        jLabel1.setText("Versão 2.6");
        jLabel1.setToolTipText("");

        jProgressBar1.setStringPainted(true);

        jProgressBar2.setStringPainted(true);

        jPanel8.setBorder(javax.swing.BorderFactory.createTitledBorder(""));

        jScrollPane2.setBorder(null);

        jTextArea2.setEditable(false);
        jTextArea2.setColumns(20);
        jTextArea2.setFont(new java.awt.Font("Miriam", 0, 13)); // NOI18N
        jTextArea2.setRows(5);
        jTextArea2.setBorder(javax.swing.BorderFactory.createCompoundBorder());
        jScrollPane2.setViewportView(jTextArea2);

        javax.swing.GroupLayout jPanel8Layout = new javax.swing.GroupLayout(jPanel8);
        jPanel8.setLayout(jPanel8Layout);
        jPanel8Layout.setHorizontalGroup(
            jPanel8Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane2)
        );
        jPanel8Layout.setVerticalGroup(
            jPanel8Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane2, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, 81, Short.MAX_VALUE)
        );

        jPanel9.setBorder(javax.swing.BorderFactory.createTitledBorder(""));

        jButton2.setIcon(new javax.swing.ImageIcon(getClass().getResource("/jprojetopharmalink/lupa16.png"))); // NOI18N
        jButton2.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton2ActionPerformed(evt);
            }
        });

        jButton3.setIcon(new javax.swing.ImageIcon(getClass().getResource("/jprojetopharmalink/processar16.png"))); // NOI18N
        jButton3.setText("Gerar");
        jButton3.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton3ActionPerformed(evt);
            }
        });

        jComboBoxProjeto.setModel(new javax.swing.DefaultComboBoxModel(new String[] { "Pharmalink", "UniLider" }));
        jComboBoxProjeto.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jComboBoxProjetoActionPerformed(evt);
            }
        });

        jLabel3.setText("Projeto");

        jLabel4.setText("Distribuidora");

        jComboBoxDistribuidora.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                jComboBoxDistribuidoraItemStateChanged(evt);
            }
        });
        jComboBoxDistribuidora.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jComboBoxDistribuidoraActionPerformed(evt);
            }
        });

        jLabelCnpj.setFont(new java.awt.Font("Tahoma", 2, 11)); // NOI18N
        jLabelCnpj.setText(" ");

        javax.swing.GroupLayout jPanel9Layout = new javax.swing.GroupLayout(jPanel9);
        jPanel9.setLayout(jPanel9Layout);
        jPanel9Layout.setHorizontalGroup(
            jPanel9Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel9Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel9Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel9Layout.createSequentialGroup()
                        .addGroup(jPanel9Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(jPanel9Layout.createSequentialGroup()
                                .addComponent(jLabel3)
                                .addGap(0, 0, Short.MAX_VALUE))
                            .addComponent(jTextField1))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButton2, javax.swing.GroupLayout.PREFERRED_SIZE, 28, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButton3))
                    .addGroup(jPanel9Layout.createSequentialGroup()
                        .addGroup(jPanel9Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                            .addGroup(jPanel9Layout.createSequentialGroup()
                                .addComponent(jLabel4)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                                .addComponent(jComboBoxDistribuidora, javax.swing.GroupLayout.PREFERRED_SIZE, 325, javax.swing.GroupLayout.PREFERRED_SIZE))
                            .addComponent(jComboBoxProjeto, javax.swing.GroupLayout.PREFERRED_SIZE, 325, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, 24, Short.MAX_VALUE)
                        .addComponent(jLabelCnpj, javax.swing.GroupLayout.PREFERRED_SIZE, 349, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addGap(12, 12, 12))
        );
        jPanel9Layout.setVerticalGroup(
            jPanel9Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel9Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel9Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jComboBoxProjeto, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabel3))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel9Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel4)
                    .addComponent(jComboBoxDistribuidora, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jLabelCnpj))
                .addGap(18, 18, 18)
                .addGroup(jPanel9Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jButton2)
                    .addComponent(jTextField1, javax.swing.GroupLayout.PREFERRED_SIZE, 25, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton3))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        jPanel10.setBorder(javax.swing.BorderFactory.createTitledBorder(""));

        jScrollPane1.setBorder(javax.swing.BorderFactory.createEmptyBorder(1, 1, 1, 1));

        jTextArea1.setColumns(20);
        jTextArea1.setRows(5);
        jTextArea1.setBorder(javax.swing.BorderFactory.createCompoundBorder());
        jScrollPane1.setViewportView(jTextArea1);

        javax.swing.GroupLayout jPanel10Layout = new javax.swing.GroupLayout(jPanel10);
        jPanel10.setLayout(jPanel10Layout);
        jPanel10Layout.setHorizontalGroup(
            jPanel10Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane1, javax.swing.GroupLayout.Alignment.TRAILING)
        );
        jPanel10Layout.setVerticalGroup(
            jPanel10Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jScrollPane1, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, 538, Short.MAX_VALUE)
        );

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jPanel9, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(jPanel8, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jProgressBar1, javax.swing.GroupLayout.PREFERRED_SIZE, 372, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jProgressBar2, javax.swing.GroupLayout.PREFERRED_SIZE, 372, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                            .addComponent(jLabel2)
                            .addComponent(jLabel1)))
                    .addComponent(jPanel10, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addContainerGap())
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jPanel9, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jPanel8, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jPanel10, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGap(18, 18, 18)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addComponent(jLabel1)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jLabel2))
                    .addGroup(jPanel1Layout.createSequentialGroup()
                        .addComponent(jProgressBar1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jProgressBar2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addGap(20, 20, 20))
        );

        jTabbedPane1.addTab("Principal", jPanel1);

        jTextArea4.setColumns(20);
        jTextArea4.setRows(5);
        jScrollPane6.setViewportView(jTextArea4);

        jPanel11.setBorder(javax.swing.BorderFactory.createTitledBorder(""));

        jList1.setFont(new java.awt.Font("Verdana", 0, 10)); // NOI18N
        jScrollPane5.setViewportView(jList1);

        jLabel10.setText("Arquivo(s) de retorno");

        jButtonFormRetornoAdicionar.setText("Adicionar");
        jButtonFormRetornoAdicionar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonFormRetornoAdicionarActionPerformed(evt);
            }
        });

        jButton11.setLabel("Verifica \nFalta");
        jButton11.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton11ActionPerformed(evt);
            }
        });

        jList2.setFont(new java.awt.Font("Verdana", 0, 10)); // NOI18N
        jList2.addListSelectionListener(new javax.swing.event.ListSelectionListener() {
            public void valueChanged(javax.swing.event.ListSelectionEvent evt) {
                jList2ValueChanged(evt);
            }
        });
        jScrollPane4.setViewportView(jList2);

        jLabel15.setText("Arquivo(s) com Falta");

        javax.swing.GroupLayout jPanel11Layout = new javax.swing.GroupLayout(jPanel11);
        jPanel11.setLayout(jPanel11Layout);
        jPanel11Layout.setHorizontalGroup(
            jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel11Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addGroup(jPanel11Layout.createSequentialGroup()
                        .addComponent(jLabel10)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                        .addComponent(jButtonFormRetornoAdicionar)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButton11))
                    .addComponent(jScrollPane5, javax.swing.GroupLayout.PREFERRED_SIZE, 376, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 32, Short.MAX_VALUE)
                .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel15)
                    .addComponent(jScrollPane4, javax.swing.GroupLayout.PREFERRED_SIZE, 376, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addContainerGap())
        );
        jPanel11Layout.setVerticalGroup(
            jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel11Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(jPanel11Layout.createSequentialGroup()
                        .addComponent(jLabel15)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jScrollPane4, javax.swing.GroupLayout.PREFERRED_SIZE, 209, javax.swing.GroupLayout.PREFERRED_SIZE))
                    .addGroup(jPanel11Layout.createSequentialGroup()
                        .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(jPanel11Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                                .addComponent(jButtonFormRetornoAdicionar)
                                .addComponent(jButton11))
                            .addComponent(jLabel10, javax.swing.GroupLayout.Alignment.TRAILING))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jScrollPane5, javax.swing.GroupLayout.PREFERRED_SIZE, 209, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        jPanel12.setBorder(javax.swing.BorderFactory.createTitledBorder(""));

        jLabel13.setText("Distribuidora origem");

        jTextFieldDistrOrigem.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jTextFieldDistrOrigemActionPerformed(evt);
            }
        });

        jLabel14.setText("Distribuidora destino");

        jComboBoxDistrRetorno.addItemListener(new java.awt.event.ItemListener() {
            public void itemStateChanged(java.awt.event.ItemEvent evt) {
                jComboBoxDistrRetornoItemStateChanged(evt);
            }
        });

        jButton10.setIcon(new javax.swing.ImageIcon(getClass().getResource("/jprojetopharmalink/processar16.png"))); // NOI18N
        jButton10.setText("Gerar");
        jButton10.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton10ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel12Layout = new javax.swing.GroupLayout(jPanel12);
        jPanel12.setLayout(jPanel12Layout);
        jPanel12Layout.setHorizontalGroup(
            jPanel12Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel12Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel12Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel13)
                    .addComponent(jTextFieldDistrOrigem, javax.swing.GroupLayout.PREFERRED_SIZE, 369, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addGroup(jPanel12Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jLabel14)
                    .addComponent(jComboBoxDistrRetorno, javax.swing.GroupLayout.PREFERRED_SIZE, 265, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addComponent(jButton10, javax.swing.GroupLayout.PREFERRED_SIZE, 90, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addContainerGap())
        );
        jPanel12Layout.setVerticalGroup(
            jPanel12Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel12Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel12Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel13)
                    .addComponent(jLabel14))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel12Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jTextFieldDistrOrigem, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jComboBoxDistrRetorno, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton10))
                .addContainerGap(28, Short.MAX_VALUE))
        );

        javax.swing.GroupLayout jPanel3Layout = new javax.swing.GroupLayout(jPanel3);
        jPanel3.setLayout(jPanel3Layout);
        jPanel3Layout.setHorizontalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel3Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jPanel11, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(jPanel12, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(jScrollPane6))
                .addContainerGap())
        );
        jPanel3Layout.setVerticalGroup(
            jPanel3Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel3Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jPanel11, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jPanel12, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jScrollPane6, javax.swing.GroupLayout.DEFAULT_SIZE, 458, Short.MAX_VALUE)
                .addContainerGap())
        );

        jTabbedPane1.addTab("Retorno", jPanel3);

        jPanel2.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jPanel2MouseClicked(evt);
            }
        });

        jPanel6.setBorder(javax.swing.BorderFactory.createTitledBorder("Caminho"));
        jPanel6.setName(""); // NOI18N

        jButton7.setText("Salvar");
        jButton7.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton7ActionPerformed(evt);
            }
        });

        jLabel7.setText("Retorno");

        jButton6.setText("...");
        jButton6.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton6ActionPerformed(evt);
            }
        });

        jButton5.setText("...");
        jButton5.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton5ActionPerformed(evt);
            }
        });

        jLabel6.setText("Envio");

        jLabel5.setText("Planilha");

        jButton4.setText("...");
        jButton4.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton4ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel6Layout = new javax.swing.GroupLayout(jPanel6);
        jPanel6.setLayout(jPanel6Layout);
        jPanel6Layout.setHorizontalGroup(
            jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel6Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jButton7)
                    .addGroup(jPanel6Layout.createSequentialGroup()
                        .addGap(2, 2, 2)
                        .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                            .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel6Layout.createSequentialGroup()
                                    .addComponent(jLabel5)
                                    .addGap(18, 18, 18))
                                .addGroup(jPanel6Layout.createSequentialGroup()
                                    .addComponent(jLabel6)
                                    .addGap(28, 28, 28)))
                            .addGroup(jPanel6Layout.createSequentialGroup()
                                .addComponent(jLabel7)
                                .addGap(18, 18, 18)))
                        .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(jPanel6Layout.createSequentialGroup()
                                .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                    .addComponent(jTextFieldCaminhoEnvio, javax.swing.GroupLayout.DEFAULT_SIZE, 465, Short.MAX_VALUE)
                                    .addComponent(jTextFieldCaminhoPlanilha))
                                .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                                    .addGroup(jPanel6Layout.createSequentialGroup()
                                        .addGap(18, 18, 18)
                                        .addComponent(jButton5, javax.swing.GroupLayout.PREFERRED_SIZE, 31, javax.swing.GroupLayout.PREFERRED_SIZE))
                                    .addGroup(jPanel6Layout.createSequentialGroup()
                                        .addGap(18, 18, 18)
                                        .addComponent(jButton4, javax.swing.GroupLayout.PREFERRED_SIZE, 31, javax.swing.GroupLayout.PREFERRED_SIZE))))
                            .addGroup(jPanel6Layout.createSequentialGroup()
                                .addComponent(jTextFieldCaminhoRetorno, javax.swing.GroupLayout.PREFERRED_SIZE, 465, javax.swing.GroupLayout.PREFERRED_SIZE)
                                .addGap(18, 18, 18)
                                .addComponent(jButton6, javax.swing.GroupLayout.PREFERRED_SIZE, 31, javax.swing.GroupLayout.PREFERRED_SIZE)))))
                .addContainerGap(207, Short.MAX_VALUE))
        );
        jPanel6Layout.setVerticalGroup(
            jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel6Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel5)
                    .addComponent(jTextFieldCaminhoPlanilha, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton4))
                .addGap(18, 18, 18)
                .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel6)
                    .addComponent(jTextFieldCaminhoEnvio, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButton5))
                .addGap(18, 18, 18)
                .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jButton6)
                    .addGroup(jPanel6Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(jTextFieldCaminhoRetorno, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(jLabel7)))
                .addGap(18, 18, 18)
                .addComponent(jButton7)
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addGap(19, 19, 19)
                .addComponent(jLabel11)
                .addContainerGap(795, Short.MAX_VALUE))
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jPanel6, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                .addContainerGap())
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jPanel6, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(282, 282, 282)
                .addComponent(jLabel11)
                .addContainerGap(364, Short.MAX_VALUE))
        );

        jTabbedPane1.addTab("Configurações", jPanel2);

        jPanel4.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                jPanel4FocusGained(evt);
            }
        });

        jTable1.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {},
                {},
                {},
                {}
            },
            new String [] {

            }
        ));
        jTable1.setSelectionMode(javax.swing.ListSelectionModel.SINGLE_INTERVAL_SELECTION);
        jTable1.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jTable1MouseClicked(evt);
            }
        });
        jTable1.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                jTable1FocusGained(evt);
            }
        });
        jTable1.addPropertyChangeListener(new java.beans.PropertyChangeListener() {
            public void propertyChange(java.beans.PropertyChangeEvent evt) {
                jTable1PropertyChange(evt);
            }
        });
        jScrollPane3.setViewportView(jTable1);

        jPanel5.setBorder(javax.swing.BorderFactory.createTitledBorder(null, "Dados do Distribuidor", javax.swing.border.TitledBorder.LEFT, javax.swing.border.TitledBorder.DEFAULT_POSITION));

        jLabel8.setText("Descrição");

        jLabel9.setText("CNPJ");

        jButton8.setText("Salvar");
        jButton8.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton8ActionPerformed(evt);
            }
        });

        jButton9.setText("Cancelar");
        jButton9.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton9ActionPerformed(evt);
            }
        });

        javax.swing.GroupLayout jPanel5Layout = new javax.swing.GroupLayout(jPanel5);
        jPanel5.setLayout(jPanel5Layout);
        jPanel5Layout.setHorizontalGroup(
            jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel5Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(jPanel5Layout.createSequentialGroup()
                        .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(jPanel5Layout.createSequentialGroup()
                                .addComponent(jLabel8)
                                .addGap(0, 0, Short.MAX_VALUE))
                            .addComponent(jTextFieldDescricao))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jLabel9)
                            .addComponent(jTextFieldCnpj, javax.swing.GroupLayout.PREFERRED_SIZE, 222, javax.swing.GroupLayout.PREFERRED_SIZE)))
                    .addGroup(jPanel5Layout.createSequentialGroup()
                        .addComponent(jButton8)
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addComponent(jButton9)
                        .addGap(0, 0, Short.MAX_VALUE)))
                .addContainerGap())
        );
        jPanel5Layout.setVerticalGroup(
            jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel5Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel8)
                    .addComponent(jLabel9))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jTextFieldDescricao, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jTextFieldCnpj, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addGroup(jPanel5Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jButton8)
                    .addComponent(jButton9))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        jPanel7.setBorder(javax.swing.BorderFactory.createTitledBorder(""));

        jButtonExcluir.setText("Excluir");
        jButtonExcluir.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonExcluirActionPerformed(evt);
            }
        });

        jButtonEditar.setText("Editar");
        jButtonEditar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonEditarActionPerformed(evt);
            }
        });

        jButtonBuscar.setText("Buscar");
        jButtonBuscar.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonBuscarActionPerformed(evt);
            }
        });

        jLabel12.setText("Pesquisa");

        javax.swing.GroupLayout jPanel7Layout = new javax.swing.GroupLayout(jPanel7);
        jPanel7.setLayout(jPanel7Layout);
        jPanel7Layout.setHorizontalGroup(
            jPanel7Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel7Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jLabel12)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jTextFieldBusca, javax.swing.GroupLayout.PREFERRED_SIZE, 271, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jButtonBuscar)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jButtonEditar)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jButtonExcluir)
                .addContainerGap(236, Short.MAX_VALUE))
        );
        jPanel7Layout.setVerticalGroup(
            jPanel7Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel7Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel7Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel12)
                    .addComponent(jTextFieldBusca, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(jButtonBuscar)
                    .addComponent(jButtonEditar)
                    .addComponent(jButtonExcluir))
                .addContainerGap(javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
        );

        javax.swing.GroupLayout jPanel4Layout = new javax.swing.GroupLayout(jPanel4);
        jPanel4.setLayout(jPanel4Layout);
        jPanel4Layout.setHorizontalGroup(
            jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel4Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane3, javax.swing.GroupLayout.DEFAULT_SIZE, 794, Short.MAX_VALUE)
                    .addComponent(jPanel5, javax.swing.GroupLayout.Alignment.TRAILING, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(jPanel7, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addContainerGap())
        );
        jPanel4Layout.setVerticalGroup(
            jPanel4Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel4Layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jPanel5, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jPanel7, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                .addComponent(jScrollPane3, javax.swing.GroupLayout.DEFAULT_SIZE, 623, Short.MAX_VALUE)
                .addContainerGap())
        );

        jTabbedPane1.addTab("Distribuidora", jPanel4);

        setJMenuBar(jMenuBar1);

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jTabbedPane1)
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addComponent(jTabbedPane1)
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    public void preencheGrid() {
        ResultSet rsdistribuidora = null;

        PreparedStatement pst = null;


        try {

            if (jTextFieldBusca.getText().equals("")) {

                pst = con.prepareStatement("SELECT "
                        + "id_distribuidora,"
                        + "descricao,"
                        + "cnpj "
                        + "FROM "
                        + "distribuidora");
            } else {
                pst = con.prepareStatement("SELECT "
                        + "id_distribuidora,"
                        + "descricao,"
                        + "cnpj "
                        + "FROM "
                        + "distribuidora where "
                        + "descricao like '" + jTextFieldBusca.getText() + "%' "
                        + "or cnpj = '" + jTextFieldBusca.getText() + "'");
            }
        } catch (SQLException ex) {
            Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rsdistribuidora = pst.executeQuery();
        } catch (SQLException ex) {
            Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
        }


        String[] tableColumnsName = {"ID", "Descrição", "CNPJ"};

        DefaultTableModel aModel = (DefaultTableModel) jTable1.getModel();
        aModel.setNumRows(0);
        aModel.setColumnIdentifiers(tableColumnsName);

        java.sql.ResultSetMetaData rsmd = null;
        try {
            rsmd = rsdistribuidora.getMetaData();
        } catch (SQLException ex) {
            Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
        }
        jTable1.setAutoResizeMode(jTable1.AUTO_RESIZE_OFF);
        jTable1.getColumnModel().getColumn(0).setPreferredWidth(60);
        jTable1.getColumnModel().getColumn(1).setPreferredWidth(551);
        jTable1.getColumnModel().getColumn(2).setPreferredWidth(200);


        int colNo = 0;
        try {
            colNo = rsmd.getColumnCount() + 1;
        } catch (SQLException ex) {
            Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            while (rsdistribuidora.next()) {
                Object[] objects = new Object[colNo];
                for (int i = 0; i < colNo - 1; i++) {
                    objects[i] = rsdistribuidora.getObject(i + 1);
                }
                objects[colNo - 1] = "P";
                aModel.addRow(objects);

            }
        } catch (SQLException ex) {
            Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
        }

        jTable1.setModel(aModel);




    }

    public void GeraArquivo() throws SQLException {
        LerCaminho lc = new LerCaminho();
        Update u = new Update();
        String idPedido = null;


        lc.RetornoCaminho();
        caminhoEnvioDefaut = lc.retcaminhoEnvio;

        String hora = "";
        String nome, datapedido, datapedidook, Texto = "\n";

        if (!jTextField1.getText().equals("")) {
            File diretorio = null;

            diretorio = new File(caminhoEnvioDefaut); // ajfilho é uma pasta!

            if (!diretorio.exists()) {
                diretorio.mkdirs(); //mkdir() cria somente um diretório, mkdirs() cria diretórios e subdiretórios.
            }

            try {

                jProgressBar1.setMinimum(0);
                jProgressBar1.setMaximum(arquivo.length);
                jProgressBar1.setValue(0);
                jProgressBar1.setStringPainted(true);
                for (int arq = 0; arq < arquivo.length; arq++) {

                    //  JOptionPane.showMessageDialog(null, "aaaa");
                    jProgressBar1.setValue(arq + 1);
                    Texto = Texto + "--------------------------- LEITURA DE " + arquivo[arq].getName() + "---------------------------\n\n";
                    jTextArea1.setText(Texto);
                    jTextArea1.setCaretPosition(jTextArea1.getText().length());
                    boolean primeiraEmpresa = true;
                    boolean criouArquivo = false;
                    String cnpj = "";
                    String cnpjdavez = "";
                    int fixo = 1;
                    int QtdeFixo7 = 0;

                    Workbook workbook = Workbook.getWorkbook(new File(arquivo[arq].getPath()));

                    Sheet sheet = workbook.getSheet(0);
                    int linhas = sheet.getRows();
                    System.out.println("Iniciando a leitura da planilha XLS:");

                    File fw = null;
                    FileOutputStream fos = null;

                    jProgressBar2.setMinimum(0);
                    jProgressBar2.setMaximum(linhas);
                    jProgressBar2.setValue(0);
                    jProgressBar2.setStringPainted(true);
                    for (int i = 10; i < linhas; i++) {
                        jProgressBar2.setValue(i + 1);

                        Cell a1 = sheet.getCell(0, i); // nome
                        Cell a2 = sheet.getCell(1, i); //cnpj
                        Cell a3 = sheet.getCell(2, i);
                        Cell a4 = sheet.getCell(3, i);
                        Cell a5 = sheet.getCell(4, i);
                        Cell a6 = sheet.getCell(5, i);//numero pedido
                        Cell a7 = sheet.getCell(6, i); //datapedido
                        Cell a8 = sheet.getCell(7, i);//codigo barras
                        Cell a9 = sheet.getCell(8, i);//nome produto
                        Cell a10 = sheet.getCell(9, i);//fabricante
                        Cell a11 = sheet.getCell(10, i); //qtde
                        Cell a15 = sheet.getCell(14, i); //codigo do distribuidor

                        String as1 = a1.getContents(); //nome
                        String as2 = a2.getContents(); //cnpj
                        String as3 = a3.getContents();
                        String as4 = a4.getContents();
                        String as5 = a5.getContents();
                        String as6 = a6.getContents(); //numero pedido
                        String as7 = a7.getContents(); //data pedido
                        String as8 = a8.getContents(); //codigo barras
                        String as9 = a9.getContents();//nome produto
                        String as10 = a10.getContents();//fabricante
                        String as11 = a11.getContents(); //qtde
                        String as15 = a15.getContents(); //codigo do distribuidor

                        cnpj = as2.replaceAll("\\.", "");
                        cnpj = cnpj.replaceAll("-", "");

                        int ini = as1.indexOf("(");
                        int fim = as1.indexOf(")");

                        if (!cnpj.equals("")) {

                            datapedido = as7.substring(0, 10);

                            datapedidook = datapedido.substring(6);
                            datapedidook = datapedidook + datapedido.substring(3, 5);
                            datapedidook = datapedidook + datapedido.substring(0, 2);

                            hora = as7.substring(11);
                            nome = as1.substring(ini + 2, fim - 1);

                            if (nome.equals("")) {
                                nome = "Sem Nome";
                            }

                            if (primeiraEmpresa) {
                                cnpjdavez = cnpj;
                                primeiraEmpresa = false;
                            }

                            if (!cnpjdavez.equals(cnpj)) {

                                if (QtdeFixo7 < 10) {
                                    fos.write(("80" + QtdeFixo7 + "0000000000000000000000000000" + System.getProperty("line.separator")).getBytes());

                                } else {
                                    fos.write(("8" + QtdeFixo7 + "0000000000000000000000000000" + System.getProperty("line.separator")).getBytes());
                                }
                                fixo = 1;
                                criouArquivo = false;
                                fos.close();
                            }

                            if (!criouArquivo) {

                                cnpjdavez = cnpj;
                                QtdeFixo7 = 0;

                                hora = hora.replaceAll(":", "");
                                fw = new File(caminhoEnvioDefaut + "\\PHL30" + cnpj + "_" + datapedidook + hora + "00.TXT");


                                idPedido = u.getId("id_pedido");

                                String arquivoGerado = "PHL30" + cnpj + "_" + datapedidook + hora + "00";
                                Date data = new Date(System.currentTimeMillis());
                                SimpleDateFormat formatador = new SimpleDateFormat("yyyy-MM-dd");
                                String dataFormatada = formatador.format(data);
                                String NumPedido = as6.replaceAll("/", "");

                                String sql = "insert into pedido values('" + idPedido + "','" + NumPedido + "','" + arquivoGerado + "','" + dataFormatada + "','" + arrayIdDistr[jComboBoxDistribuidora.getSelectedIndex()] + "','" + as1 + "')";
                                u.setUpdateControle(sql);


                                int intid = Integer.parseInt(idPedido) + 1;
                                u.setUpdateControle("update controle set valor = '" + intid + "' where campo = 'id_pedido'");

                                Texto = Texto + "Loja: " + nome + "\n";
                                Texto = Texto + "Caminho do arquivo:\n";
                                Texto = Texto + caminhoEnvioDefaut + "\\PHL30" + cnpj + "_" + datapedidook + hora + "00.TXT\n\n";
                                jTextArea1.setText(Texto);
                                jTextArea1.setCaretPosition(jTextArea1.getText().length());
                                fos = new FileOutputStream(fw, false);
                            }

                            if (fixo == 1) {
                                fos.write(("1" + cnpj + "1000000000000030" + System.getProperty("line.separator")).getBytes());
                                fixo = 2;
                            }
                            if (fixo == 2) {
                                if (jComboBoxDistribuidora.getSelectedIndex() == 0) {
                                    fos.write(("2000000000000000000000000000000" + System.getProperty("line.separator")).getBytes());
                                } else {

                                    fos.write(("200000000" + jLabelCnpj.getText() + "00000000" + System.getProperty("line.separator")).getBytes());
                                }
                                fixo = 3;
                            }
                            if (fixo == 3) {
                                fos.write(("3000000000000000000000000000000" + System.getProperty("line.separator")).getBytes());
                                fixo = 4;
                            }
                            if (fixo == 4) {

                                String NumPedido = as6.replaceAll("/", "");
                                int tamanho = NumPedido.length();
                                for (int j = tamanho; j < 15; j++) {
                                    NumPedido = "0" + NumPedido;
                                }

                                fos.write(("4" + NumPedido + "000000000000000" + System.getProperty("line.separator")).getBytes());
                                fixo = 5;
                            }
                            if (fixo == 5) {
                                datapedido = datapedido.replaceAll("/", "");
                                fos.write(("5" + datapedido + "0000000000000000000000" + System.getProperty("line.separator")).getBytes());
                                fixo = 6;
                            }
                            if (fixo == 6) {
                                hora = hora.replaceAll(":", "");
                                fos.write(("6" + hora + "00000000000000000000000000" + System.getProperty("line.separator")).getBytes());
                                fixo = 7;
                            }
                            if (fixo == 7) {
                                String idPedidoItem;
                                idPedidoItem = u.getId("id_pedido_item");

                                String strQtde = as11.replaceAll(",", "\\.");
                                double dlqtde = Double.parseDouble(strQtde);
                                int qtde = (int) dlqtde;

                                strQtde = String.valueOf(qtde);
                                int tamanho = strQtde.length();
                                for (int j = tamanho; j < 4; j++) {
                                    strQtde = "0" + strQtde;
                                }
                                fos.write(("7" + as8 + strQtde + "0000000000000" + System.getProperty("line.separator")).getBytes());
                                String sql = "insert into pedido_item values ('" + idPedidoItem + "','" + as9 + "','" + as8 + "','" + strQtde + "','" + as10 + "','" + idPedido + "')";
                                u.setUpdateControle(sql);

                                int intid = Integer.parseInt(idPedidoItem) + 1;
                                u.setUpdateControle("update controle set valor = '" + intid + "' where campo = 'id_pedido_item'");

                                QtdeFixo7 += 1;
                                criouArquivo = true;
                            }

                        }

                    }

                    workbook.close();

                    if (QtdeFixo7 < 10) {
                        fos.write(("80" + QtdeFixo7 + "0000000000000000000000000000" + System.getProperty("line.separator")).getBytes());
                        fos.close();
                    } else {
                        fos.write(("8" + QtdeFixo7 + "0000000000000000000000000000" + System.getProperty("line.separator")).getBytes());
                        fos.close();
                    }
                }
                jTextArea1.setText(Texto);
                jTextArea1.setCaretPosition(jTextArea1.getText().length());
                JOptionPane.showMessageDialog(null, "Arquivo(s) gerado(s) com sucesso!");



            } catch (IOException ex) {
                Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
            } catch (BiffException ex) {
                Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    public void GeraArquivoUniLider() throws SQLException {
        LerCaminho lc = new LerCaminho();

        lc.RetornoCaminho();
        caminhoEnvioDefaut = lc.retcaminhoEnvio;

        String hora = "";
        String nome, datapedido, datapedidook, Texto = "\n";

        if (!jTextField1.getText().equals("")) {
            File diretorio = null;

            diretorio = new File(caminhoEnvioDefaut); // ajfilho é uma pasta!

            if (!diretorio.exists()) {
                diretorio.mkdirs(); //mkdir() cria somente um diretório, mkdirs() cria diretórios e subdiretórios.
            }

            try {

                jProgressBar1.setMinimum(0);
                jProgressBar1.setMaximum(arquivo.length);
                jProgressBar1.setValue(0);
                jProgressBar1.setStringPainted(true);
                for (int arq = 0; arq < arquivo.length; arq++) {

                    //  JOptionPane.showMessageDialog(null, "aaaa");
                    jProgressBar1.setValue(arq + 1);
                    Texto = Texto + "--------------------------- LEITURA DE " + arquivo[arq].getName() + "---------------------------\n\n";
                    jTextArea1.setText(Texto);
                    jTextArea1.setCaretPosition(jTextArea1.getText().length());
                    boolean primeiraEmpresa = true;
                    boolean comecou = false;
                    String cnpj = "";
                    String cnpjdavez = "";
                    int fixo = 1;
                    int QtdeFixo7 = 0;

                    Workbook workbook = Workbook.getWorkbook(new File(arquivo[arq].getPath()));

                    Sheet sheet = workbook.getSheet(0);
                    int linhas = sheet.getRows();
                    System.out.println("Iniciando a leitura da planilha XLS:");

                    File fw = null;
                    FileOutputStream fos = null;
                    int hj = arq+1;
                    fw = new File("c:\\Unilider\\servcli\\UNILIDER"+hj+".PED");
                    fos = new FileOutputStream(fw, false);
                    jProgressBar2.setMinimum(0);
                    jProgressBar2.setMaximum(linhas);
                    jProgressBar2.setValue(0);
                    jProgressBar2.setStringPainted(true);
                    for (int i = 10; i < linhas; i++) {
                        jProgressBar2.setValue(i + 1);

                        Cell a1 = sheet.getCell(0, i); // nome
                        Cell a2 = sheet.getCell(1, i); //cnpj
                        Cell a3 = sheet.getCell(2, i);
                        Cell a4 = sheet.getCell(3, i);
                        Cell a5 = sheet.getCell(4, i);
                        Cell a6 = sheet.getCell(5, i);//numero pedido
                        Cell a7 = sheet.getCell(6, i); //datapedido
                        Cell a8 = sheet.getCell(7, i);//codigo barras
                        Cell a9 = sheet.getCell(8, i);//nome produto
                        Cell a10 = sheet.getCell(9, i);//fabricante
                        Cell a11 = sheet.getCell(10, i); //qtde
                        Cell a15 = sheet.getCell(14, i); //codigo do distribuidor

                        String nomeEmp = a1.getContents(); //nome
                        String cnpjEmp = a2.getContents(); //cnpj
                        String as3 = a3.getContents();
                        String as4 = a4.getContents();
                        String as5 = a5.getContents();
                        String numPed = a6.getContents(); //numero pedido
                        String as7 = a7.getContents(); //data pedido
                        String codProd = a8.getContents(); //codigo barras
                        String as9 = a9.getContents();//nome produto
                        String as10 = a10.getContents();//fabricante
                        String qtdeProd = a11.getContents(); //qtde
                        String codDistr = a15.getContents(); //codigo do distribuidor

                        cnpj = cnpjEmp.replaceAll("\\.", "");
                        cnpj = cnpj.replaceAll("-", "");

                        int ini = nomeEmp.indexOf("(");
                        int fim = nomeEmp.indexOf(")");

                        if (!cnpj.equals("")) {

                            datapedido = as7.substring(0, 10);

                            datapedidook = datapedido.substring(6);
                            datapedidook = datapedidook + datapedido.substring(3, 5);
                            datapedidook = datapedidook + datapedido.substring(0, 2);

                            hora = as7.substring(11);
                            nome = nomeEmp.substring(ini + 2, fim - 1);

                            if (nome.equals("")) {
                                nome = "Sem Nome";
                            }

                            if (primeiraEmpresa) {
                                cnpjdavez = cnpj;
                                primeiraEmpresa = false;
                            }

                            if (!cnpjdavez.equals(cnpj)) {

                                String strQtdeFixo7 = String.valueOf(QtdeFixo7);
                                int tam2 = strQtdeFixo7.length();
                                for (int h = tam2; h < 25; h++) {
                                    strQtdeFixo7 = "0" + strQtdeFixo7;
                                }

                                fos.write(("8" + strQtdeFixo7 + System.getProperty("line.separator")).getBytes());

                                fixo = 1;
                                
                                comecou = false;
                                //          fos.close();
                            }

                            if (!comecou) {
                                Texto = Texto + nomeEmp +" - "+cnpjEmp+"\n";
                                cnpjdavez = cnpj;
                                QtdeFixo7 = 0;



                                fixo = 1;
                            }

                            if (fixo == 1) {
                                int tamanho = codDistr.length();
                                for (int j = tamanho; j < 8; j++) {
                                    codDistr = "0" + codDistr;
                                }
                                fos.write(("1" + codDistr + "FF               " + System.getProperty("line.separator")).getBytes());
                                fixo = 2;
                            }
                            if (fixo == 2) {
                                String NumPedido = numPed.replaceAll("/", "");
                                int tamanho = NumPedido.length();
                                for (int j = tamanho; j < 10; j++) {
                                    NumPedido = "0" + NumPedido;
                                }

                                fos.write(("2" + NumPedido + "               " + System.getProperty("line.separator")).getBytes());

                                fixo = 3;
                            }
                            if (fixo == 3) {
                                fos.write(("3                         " + System.getProperty("line.separator")).getBytes());
                                fixo = 4;
                            }
                            if (fixo == 4) {

                                fos.write(("4                         " + System.getProperty("line.separator")).getBytes());
                                fixo = 5;
                            }
                            if (fixo == 5) {
                                Date data = new Date(System.currentTimeMillis());
                                SimpleDateFormat formatador = new SimpleDateFormat("yyyyMMdd");
                                String dataFormatada = formatador.format(data);

                                fos.write(("5" + dataFormatada + "                 " + System.getProperty("line.separator")).getBytes());
                                fixo = 6;
                            }
                            if (fixo == 6) {
                                Date horaA = new Date(System.currentTimeMillis());
                                SimpleDateFormat formatador = new SimpleDateFormat("hhmm");
                                String horaFormatada = formatador.format(horaA);
                                fos.write(("6" + horaFormatada + "                     " + System.getProperty("line.separator")).getBytes());
                                fixo = 7;
                            }
                            if (fixo == 7) {
                                                           
                                String strQtde = qtdeProd.replaceAll(",", "\\.");
                                double dlqtde = Double.parseDouble(strQtde);
                                int qtde = (int) dlqtde;

                                strQtde = String.valueOf(qtde);
                                int tamanho = strQtde.length();
                                for (int j = tamanho; j < 5; j++) {
                                    strQtde = "0" + strQtde;
                                }
                                codProd = codProd.substring(3);
                                int tam = codProd.length();
                                for (int h = tam; h < 10; h++) {
                                    codProd = "0" + codProd;
                                }
                                codProd = codProd.substring(0, 10);
                                fos.write(("7" + codProd + strQtde + "          " + System.getProperty("line.separator")).getBytes());

                                QtdeFixo7 += 1;
                                comecou = true;
                            }

                        }

                    }

                    workbook.close();

                    String strQtdeFixo7 = String.valueOf(QtdeFixo7);
                    int tam2 = strQtdeFixo7.length();
                    for (int h = tam2; h < 25; h++) {
                        strQtdeFixo7 = "0" + strQtdeFixo7;
                    }

                    strQtdeFixo7 = String.valueOf(QtdeFixo7);
                    int tam3 = strQtdeFixo7.length();
                    for (int h = tam3; h < 25; h++) {
                        strQtdeFixo7 = "0" + strQtdeFixo7;
                    }

                    fos.write(("8" + strQtdeFixo7 + System.getProperty("line.separator")).getBytes());
                    fos.close();
                    Texto = Texto + "\n\n";
                }
                jTextArea1.setText(Texto);
                jTextArea1.setCaretPosition(jTextArea1.getText().length());
                JOptionPane.showMessageDialog(null, "Arquivo(s) gerado(s) com sucesso!");



            } catch (IOException ex) {
                Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
            } catch (BiffException ex) {
                Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }

    private void jTabbedPane1FocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_jTabbedPane1FocusGained
    }//GEN-LAST:event_jTabbedPane1FocusGained

    private void jTabbedPane1MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jTabbedPane1MouseClicked
    }//GEN-LAST:event_jTabbedPane1MouseClicked

    private void formWindowOpened(java.awt.event.WindowEvent evt) {
        new Thread("Rotinas Iniciais") {
            @Override
            public void run() {
                try {
                    GeraArquivo();
                } catch (SQLException ex) {
                    Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }.start();
    }

    private void formWindowOpenedUniLider(java.awt.event.WindowEvent evt) {
        new Thread("Rotinas Iniciais") {
            public void run() {
                try {
                    GeraArquivoUniLider();
                } catch (SQLException ex) {
                    Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        }.start();
    }
    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
        if (jComboBoxProjeto.getSelectedIndex() == 0) {
            formWindowOpened(null);
        } else {
            formWindowOpenedUniLider(null);

        }

    }//GEN-LAST:event_jButton3ActionPerformed

    private void jButton2ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton2ActionPerformed

        LerCaminho lc = new LerCaminho();

        lc.RetornoCaminho();

        JFileChooser fc = new JFileChooser();
        FileNameExtensionFilter filter = new FileNameExtensionFilter("Arquivo de Excel", "xls");
        File diretorio = new File(lc.retcaminhoPlaninha);
        String linha = "", texto = "";
        int tamanhoLinha = 0;
        boolean proxima = false;

        fc.setMultiSelectionEnabled(true);
        fc.setCurrentDirectory(diretorio);
        fc.addChoosableFileFilter(filter);
        fc.setAcceptAllFileFilterUsed(false);
        fc.setFileFilter(filter);
        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {
            arquivo = fc.getSelectedFiles();
            jTextField1.setText(arquivo[0].getParent());
            for (int i = 0; i < arquivo.length; i++) {

                tamanhoLinha = tamanhoLinha + arquivo[i].getName().length();

                if (tamanhoLinha <= 90) {

                    linha = linha + arquivo[i].getName() + "; ";

                    tamanhoLinha = linha.length();

                } else {

                    linha = linha + "\n" + arquivo[i].getName() + "; ";
                    tamanhoLinha = 0;

                }

            }

            jTextArea2.setText(linha);

        } else {
            JOptionPane.showMessageDialog(null, "Você não selecionou nenhum arquivo.");
        }        // TODO add your handling code here:
    }//GEN-LAST:event_jButton2ActionPerformed

    private void jButtonBuscarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonBuscarActionPerformed
        preencheGrid();
    }//GEN-LAST:event_jButtonBuscarActionPerformed

    private void jTable1MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jTable1MouseClicked
        int linha = jTable1.getSelectedRow();
        Object objId = jTable1.getValueAt(linha, 0);
        idSelecionado = objId.toString();

    }//GEN-LAST:event_jTable1MouseClicked

    private void jButtonEditarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonEditarActionPerformed
        int linha;
        String id, descricao, cnpj;
        linha = jTable1.getSelectedRow();

        Object objId = jTable1.getValueAt(linha, 0);
        Object objDesc = jTable1.getValueAt(linha, 1);
        Object objCnpj = jTable1.getValueAt(linha, 2);

        id = objId.toString();
        descricao = objDesc.toString();
        cnpj = objCnpj.toString();

        if (!id.equals("0")) {
            jTextFieldDescricao.setText(descricao);
            jTextFieldCnpj.setText(cnpj);
            idDistribuidora = id;

            modo = "editar";
        } else {
            JOptionPane.showMessageDialog(null, "Distribuidora não pode ser alterada.");
        }

    }//GEN-LAST:event_jButtonEditarActionPerformed

    private void jButtonExcluirActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonExcluirActionPerformed
        if (!idDistribuidora.equals("0")) {
            if (idDistribuidora != null) {
                Update u = new Update();
                try {
                    u.setDelete("delete from distribuidora where id_distribuidora = " + idDistribuidora);
                    idDistribuidora = null;
                    preencheGrid();
                } catch (IOException ex) {
                    Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        } else {
            JOptionPane.showMessageDialog(null, "Distribuidora não pode ser excluida.");
        }
    }//GEN-LAST:event_jButtonExcluirActionPerformed

    private void jButton8ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton8ActionPerformed
        if (modo == "editar") {
            if (!jTextFieldDescricao.getText().equals("") || !jTextFieldCnpj.getText().equals("")) {
                if (jTextFieldDescricao.getText().length() <= 30) {
                    if (jTextFieldCnpj.getText().length() <= 14) {
                        Update u = new Update();
                        try {
                            u.setUpdate("update distribuidora set descricao = '" + jTextFieldDescricao.getText() + "', cnpj = '" + jTextFieldCnpj.getText() + "' where id_distribuidora = " + idDistribuidora);
                            jTextFieldCnpj.setText("");
                            jTextFieldDescricao.setText("");
                            modo = null;
                            preencheGrid();
                        } catch (IOException ex) {
                            Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
                        }
                    } else {
                        JOptionPane.showMessageDialog(null, "Campo cnpj não pode ultrapassar de 14 digitos");
                    }
                } else {
                    JOptionPane.showMessageDialog(null, "Campo descrição não pode ultrapassar de 30 digitos");
                }
            } else {
                JOptionPane.showMessageDialog(null, "Favor preencher todos os campos");
            }

        } else {

            String id = null;

            if (!jTextFieldDescricao.getText().equals("") || !jTextFieldCnpj.getText().equals("")) {



                try {
                    if (jTextFieldDescricao.getText().length() <= 30) {
                        if (jTextFieldCnpj.getText().length() <= 14) {
                            Update u = new Update();
                            id = u.getId("id_distribuidora");

                            u.setUpdate("insert into distribuidora values ('" + id + "','" + jTextFieldDescricao.getText() + "','" + jTextFieldCnpj.getText() + "')");

                            int intid = Integer.parseInt(id) + 1;
                            u.setUpdateControle("update controle set valor = '" + intid + "' where campo = 'id_distribuidora'");

                            preencheGrid();
                            jTextFieldDescricao.setText("");
                            jTextFieldCnpj.setText("");
                        } else {
                            JOptionPane.showMessageDialog(null, "Campo cnpj não pode ultrapassar de 14 digitos");
                        }
                    } else {
                        JOptionPane.showMessageDialog(null, "Campo descrição não pode ultrapassar de 30 digitos");
                    }

                } catch (IOException ex) {
                    Logger.getLogger(TelaCadastro.class.getName()).log(Level.SEVERE, null, ex);
                }


            } else {
                JOptionPane.showMessageDialog(null, "Favor preencher todos os campos");
            }
        }
    }//GEN-LAST:event_jButton8ActionPerformed

    private void jButton9ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton9ActionPerformed
        jTextFieldDescricao.setText("");
        jTextFieldCnpj.setText("");
        idDistribuidora = null;
        modo = null;
    }//GEN-LAST:event_jButton9ActionPerformed

    private void jTable1PropertyChange(java.beans.PropertyChangeEvent evt) {//GEN-FIRST:event_jTable1PropertyChange
    }//GEN-LAST:event_jTable1PropertyChange

    private void jTable1FocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_jTable1FocusGained
        int linha;
        String id;
        linha = jTable1.getSelectedRow();

        Object objId = jTable1.getValueAt(linha, 0);

        id = objId.toString();

        idDistribuidora = id;
    }//GEN-LAST:event_jTable1FocusGained

    private void jPanel4FocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_jPanel4FocusGained
    }//GEN-LAST:event_jPanel4FocusGained

    private void jTabbedPane1KeyPressed(java.awt.event.KeyEvent evt) {//GEN-FIRST:event_jTabbedPane1KeyPressed
    }//GEN-LAST:event_jTabbedPane1KeyPressed

    private void jTabbedPane1StateChanged(javax.swing.event.ChangeEvent evt) {//GEN-FIRST:event_jTabbedPane1StateChanged
        //configuracoes
        if (jTabbedPane1.getSelectedIndex() == 2) {
            LerCaminho lcp = new LerCaminho();
            lcp.RetornoCaminho();

            jTextFieldCaminhoPlanilha.setText(lcp.retcaminhoPlaninha);
            jTextFieldCaminhoEnvio.setText(lcp.retcaminhoEnvio);
            jTextFieldCaminhoRetorno.setText(lcp.retcaminhoRetorno);

        }
        //distribuidora
        if (jTabbedPane1.getSelectedIndex() == 3) {
            preencheGrid();
        }
        //principal
        if (jTabbedPane1.getSelectedIndex() == 0 || jTabbedPane1.getSelectedIndex() == 1) {
            try {
                preencheComboDistribuidor();
            } catch (SQLException ex) {
                Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
            }

        }
    }//GEN-LAST:event_jTabbedPane1StateChanged
    public void preencheComboDistribuidor() throws SQLException {

        int count = 0;
        con = Conexao.getConnetion();
        PreparedStatement pstContador = null;
        PreparedStatement pstDistribuidor = null;

        pstDistribuidor = con.prepareStatement("Select * from distribuidora");
        pstContador = con.prepareStatement("Select * from distribuidora");

        ResultSet rsDistribuidor = null;
        ResultSet rsContador = null;

        rsDistribuidor = pstDistribuidor.executeQuery();
        rsContador = pstContador.executeQuery();

        while (rsContador.next()) {

            count += 1;

        }

        arrayCnpj = new String[count];
        arrayIdDistr = new String[count];
        Object[] itens = new Object[count];

        count = 0;
        while (rsDistribuidor.next()) {
            arrayCnpj[count] = rsDistribuidor.getString(3);
            arrayIdDistr[count] = rsDistribuidor.getString(1);
            itens[count] = rsDistribuidor.getString("descricao");
            count += 1;

        }

        DefaultComboBoxModel model = new DefaultComboBoxModel(itens);
        jComboBoxDistribuidora.setModel(model);
        jComboBoxDistrRetorno.setModel(model);
        jLabelCnpj.setText(arrayCnpj[jComboBoxDistribuidora.getSelectedIndex()]);
        if (jTabbedPane1.getSelectedIndex() == 1) {
            idDistrRet = arrayIdDistr[jComboBoxDistrRetorno.getSelectedIndex()];
        }

    }
    private void jComboBoxProjetoActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jComboBoxProjetoActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jComboBoxProjetoActionPerformed

    private void jPanel2MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jPanel2MouseClicked
        // TODO add your handling code here:
    }//GEN-LAST:event_jPanel2MouseClicked

    private void jButton7ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton7ActionPerformed
        LerCaminho lc = new LerCaminho();
        try {
            lc.GravaCaminho(jTextFieldCaminhoPlanilha.getText(), jTextFieldCaminhoEnvio.getText(), jTextFieldCaminhoRetorno.getText());
        } catch (FileNotFoundException ex) {
            Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
        } catch (IOException ex) {
            Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
        }
    }//GEN-LAST:event_jButton7ActionPerformed

    private void jButton6ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton6ActionPerformed
        JFileChooser fc = new JFileChooser();
        fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
        File diretorio = new File("C:\\");

        fc.setCurrentDirectory(diretorio);

        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {

            diretorio = fc.getSelectedFile();
            jTextFieldCaminhoRetorno.setText(diretorio.getAbsolutePath());

        } else {
        }
    }//GEN-LAST:event_jButton6ActionPerformed

    private void jButton5ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton5ActionPerformed
        JFileChooser fc = new JFileChooser();
        fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
        File diretorio = new File("C:\\");

        fc.setCurrentDirectory(diretorio);

        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {

            diretorio = fc.getSelectedFile();
            jTextFieldCaminhoEnvio.setText(diretorio.getAbsolutePath());

        } else {
        }
    }//GEN-LAST:event_jButton5ActionPerformed

    private void jButton4ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton4ActionPerformed
        JFileChooser fc = new JFileChooser();
        fc.setFileSelectionMode(JFileChooser.DIRECTORIES_ONLY);
        File diretorio = new File("C:\\");

        fc.setCurrentDirectory(diretorio);

        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {

            diretorio = fc.getSelectedFile();
            jTextFieldCaminhoPlanilha.setText(diretorio.getAbsolutePath());

        } else {
        }
    }//GEN-LAST:event_jButton4ActionPerformed

    private void jComboBoxDistribuidoraActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jComboBoxDistribuidoraActionPerformed


        jLabelCnpj.setText(arrayCnpj[jComboBoxDistribuidora.getSelectedIndex()]);


    }//GEN-LAST:event_jComboBoxDistribuidoraActionPerformed

    private void jComboBoxDistribuidoraItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_jComboBoxDistribuidoraItemStateChanged
    }//GEN-LAST:event_jComboBoxDistribuidoraItemStateChanged

    private void jButtonFormRetornoAdicionarActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonFormRetornoAdicionarActionPerformed
        LerCaminho lc = new LerCaminho();

        lc.RetornoCaminho();

        JFileChooser fc = new JFileChooser();
        FileNameExtensionFilter filter = new FileNameExtensionFilter("Arquivo de Retorno", "ret");
        File diretorio = new File(lc.retcaminhoRetorno);
        String linha = "", texto = "";

        fc.setMultiSelectionEnabled(true);
        fc.setCurrentDirectory(diretorio);
        fc.addChoosableFileFilter(filter);
        fc.setAcceptAllFileFilterUsed(false);
        fc.setFileFilter(filter);
        int res = fc.showOpenDialog(null);

        if (res == JFileChooser.APPROVE_OPTION) {
            arquivo = fc.getSelectedFiles();

            DefaultListModel lista = new DefaultListModel();
            for (int i = 0; i < arquivo.length; i++) {
                lista.addElement(arquivo[i].getName());
                caminhoArquivo = arquivo[i].getParent();
                caminhoRetornoDefaut = caminhoArquivo;

            }

            jList1.setModel(lista);

        } else {
            JOptionPane.showMessageDialog(null, "Você não selecionou nenhum arquivo.");
        }
    }//GEN-LAST:event_jButtonFormRetornoAdicionarActionPerformed

    private void jButton11ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton11ActionPerformed
        DefaultListModel lista = new DefaultListModel();
        DefaultListModel lista2 = new DefaultListModel();

        for (int i = 0; i < jList1.getModel().getSize(); i++) {

            jList1.setSelectedIndex(i);
            try {
                //parei aqui
                if (verificaRetorno((String) jList1.getSelectedValue(), caminhoArquivo)) {

                    lista2.addElement(jList1.getSelectedValue());


                } else {
                    //     lista.addElement(jList1.getSelectedValue());
                    File diretorio = new File(caminhoArquivo + "\\" + jList1.getSelectedValue());
                    deleteTree(diretorio);
                }
            } catch (SQLException ex) {
                Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
            } catch (IOException ex) {
                Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
            }


        }

        jList1.setModel(lista);

        if (!lista2.isEmpty()) {
            jList2.setModel(lista2);
        }

    }//GEN-LAST:event_jButton11ActionPerformed

    private void jTextFieldDistrOrigemActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jTextFieldDistrOrigemActionPerformed
        // TODO add your handling code here:
    }//GEN-LAST:event_jTextFieldDistrOrigemActionPerformed

    private void jList2ValueChanged(javax.swing.event.ListSelectionEvent evt) {//GEN-FIRST:event_jList2ValueChanged
        if (jList2.getModel().getSize() > 0) {


            try {
                int posicao;
                String arquivoSemExt;
                posicao = jList2.getSelectedValue().toString().indexOf(".");
                String sqlDistr = "";
                //Apenas o nome do arquivo sem extensao
                arquivoSemExt = jList2.getSelectedValue().toString().substring(0, posicao);

                Connection con = Conexao.getConnetion();

                sqlDistr = "Select pedido.*,distribuidora.* from pedido "
                        + "left outer join distribuidora on pedido.id_distribuidora = distribuidora.id_distribuidora "
                        + "where pedido.nome_arquivo = '" + arquivoSemExt + "'";

                PreparedStatement pstDistr = con.prepareStatement(sqlDistr);
                ResultSet rsDistr = pstDistr.executeQuery();

                while (rsDistr.next()) {
                    jTextFieldDistrOrigem.setText(rsDistr.getString("descricao"));
                    cnpjPararetorno = rsDistr.getString("cnpj");

                    pedidoPararetorno = rsDistr.getString("num_pedido") + "1";
                }


            } catch (SQLException ex) {
                Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
            }


        }

    }//GEN-LAST:event_jList2ValueChanged

    private void jButton10ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton10ActionPerformed
        DefaultListModel lista = new DefaultListModel();
        DefaultListModel lista2 = new DefaultListModel();
        String Texto = "";
        LerCaminho lcp = new LerCaminho();
        lcp.RetornoCaminho();

        caminhoPlaninhaDefaut = lcp.retcaminhoPlaninha;
        caminhoEnvioDefaut = lcp.retcaminhoEnvio;
        caminhoRetornoDefaut = lcp.retcaminhoRetorno;


        for (int i = 0; i < jList2.getModel().getSize(); i++) {
            jList2.setSelectedIndex(i);
            try {
                //parei aqui
                Texto = Texto + geraArquivoRetorno((String) jList2.getSelectedValue(), caminhoArquivo);
                if (!Texto.equals("")) {
                    jTextArea4.setText(Texto);
                    File diretorio = new File(caminhoArquivo + "\\" + jList2.getSelectedValue());
                    deleteTree(diretorio);

                } else {
                    lista.addElement(jList2.getSelectedValue());
                }
            } catch (SQLException | IOException ex) {
                Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
            }


        }
        if (lista.isEmpty()) {
            lista.addElement("");
        }
        jList2.setModel(lista2);

    }//GEN-LAST:event_jButton10ActionPerformed
    public static void deleteTree(File inFile) {
        if (inFile.isFile()) {
            inFile.delete();
        } else {
            File files[] = inFile.listFiles();
            for (int i = 0; i < files.length; i++) {
                deleteTree(files[i]);
            }
        }
    }

    private void jComboBoxDistrRetornoItemStateChanged(java.awt.event.ItemEvent evt) {//GEN-FIRST:event_jComboBoxDistrRetornoItemStateChanged
        idDistrRet = arrayIdDistr[jComboBoxDistrRetorno.getSelectedIndex()];
    }//GEN-LAST:event_jComboBoxDistrRetornoItemStateChanged
    public String geraArquivoRetorno(String nomeArquivo, String caminhoArquivoRet) throws FileNotFoundException, IOException, SQLException {
        int posicao;
        String arquivoSemExt;
        posicao = nomeArquivo.indexOf(".");
        boolean isGerou = false;
        String texto = null;
        //Apenas o nome do arquivo sem extensao
        arquivoSemExt = nomeArquivo.substring(0, posicao);
        texto = "--------------------------------------------------------------------------------------------"
                + "--------------------------------------------------\n";
        texto = texto + "Arquivo de Retorno : " + caminhoArquivoRet + "\\" + nomeArquivo + "\n\n";
        String codigoBarras = "";
        String qtde = "";
        String produto = "";

        String codigoBarrasRet = "";
        String qtdeRet = "";

        String idPedido = "";
        String cnpj = "";

        Connection con = Conexao.getConnetion();

        FileReader arquivoCaminho = null;
        File cam = null;
        File fw = null;
        FileOutputStream fos = null;

        cam = new File(caminhoArquivoRet + "\\" + nomeArquivo);
        arquivoCaminho = new FileReader(cam);

        int i = 0;


        BufferedReader buffer = new BufferedReader(arquivoCaminho);
        String linha = buffer.readLine();
        i = 0;
        int QtdeItensStr = 0;

        Date data1 = new Date(System.currentTimeMillis());
        SimpleDateFormat formatador1 = new SimpleDateFormat("yyyyMMdd");
        SimpleDateFormat formatadorHora1 = new SimpleDateFormat("HHmmss");
        String dataFormatada1 = formatador1.format(data1);
        String horaFormatada1 = formatadorHora1.format(data1);

        String novoArq = arquivoSemExt.substring(0, 20) + dataFormatada1 + horaFormatada1;

        fw = new File(caminhoEnvioDefaut + "\\" + novoArq + ".TXT");



        texto = texto + "Arquivo de Envio : " + caminhoEnvioDefaut + "\\" + novoArq + ".TXT" + "\n\n";
        fos = new FileOutputStream(fw, false);
        while (i == 0) {

            if (linha.substring(0, 2).equals("07")) {
                i = 1;
                buffer.close();

                if (QtdeItensStr < 10) {
                    fos.write(("80" + QtdeItensStr + "0000000000000000000000000000" + System.getProperty("line.separator")).getBytes());
                    fos.close();
                    isGerou = true;

                } else {
                    fos.write(("8" + QtdeItensStr + "0000000000000000000000000000" + System.getProperty("line.separator")).getBytes());
                    fos.close();
                    isGerou = true;
                }

            } else {

                if (linha.substring(0, 2).equals("01")) {
                    System.out.println("cnpj do retorno linha 01 " + linha.substring(2, 16));
                    fos.write(("1" + linha.substring(2, 16) + "1000000000000030" + System.getProperty("line.separator")).getBytes());
                    System.out.println("cnpj do retorno linha 01 " + linha.substring(2, 16));
                }
                if (linha.substring(0, 2).equals("02")) {

                    fos.write(("200000000" + jLabelCnpj.getText() + "00000000" + System.getProperty("line.separator")).getBytes());

                    System.out.println("cnpj do retorno linha 02 " + jLabelCnpj.getText());
                }
                if (linha.substring(0, 2).equals("03")) {

                    fos.write(("3000000000000000000000000000000" + System.getProperty("line.separator")).getBytes());

                    System.out.println("linha 03 ");
                }

                if (linha.substring(0, 2).equals("04")) {
                    String numPed = pedidoPararetorno;
                    while (numPed.length() < 15) {
                        numPed = "0" + numPed;
                    }

                    fos.write(("4" + numPed + "000000000000000" + System.getProperty("line.separator")).getBytes());

                    System.out.println("linha 04 ");
                }
                if (linha.substring(0, 2).equals("05")) {
                    Date data = new Date(System.currentTimeMillis());
                    SimpleDateFormat formatador = new SimpleDateFormat("ddMMyyyy");
                    SimpleDateFormat formatadorHora = new SimpleDateFormat("HHmmss");
                    String dataFormatada = formatador.format(data);
                    String horaFormatada = formatadorHora.format(data);
                    fos.write(("5" + dataFormatada + "0000000000000000000000" + System.getProperty("line.separator")).getBytes());
                    fos.write(("6" + horaFormatada + "000000000000000000000000" + System.getProperty("line.separator")).getBytes());

                    System.out.println(dataFormatada + "linha 05 e 06");
                }




                System.out.println(linha.substring(0, 2));
                if (linha.substring(0, 2).equals("06")) {


                    System.out.println(linha.substring(2, 15));
                    PreparedStatement pstSelectPedido = con.prepareStatement(
                            "Select pedido.*,pedido_item.* from pedido "
                            + "left outer join pedido_item on pedido.id_pedido = pedido_item.id_pedido "
                            + "where pedido.id_pedido in (select max(id_pedido) as id_pedido from pedido_item group by pedido_item.codigo_barras) and "
                            + "pedido.nome_arquivo = '" + arquivoSemExt + "' and pedido_item.codigo_barras = '" + linha.substring(2, 15) + "'");

                    ResultSet rsSelectPedido = pstSelectPedido.executeQuery();

                    while (rsSelectPedido.next()) {

                        codigoBarras = rsSelectPedido.getString("codigo_barras");
                        qtde = rsSelectPedido.getString("quantidade");
                        produto = rsSelectPedido.getString("produto");
                        idPedido = rsSelectPedido.getString("id_pedido");
                        System.out.println(linha.substring(15, 19));
                        int total = (Integer.parseInt(qtde) - Integer.parseInt(linha.substring(15, 19)));


                        if (total > 0) {

                            String qtdeStr = qtde.toString();

                            while (qtdeStr.length() < 4) {
                                qtdeStr = "0" + qtdeStr;

                            }
                            QtdeItensStr += 1;
                            fos.write(("7" + codigoBarras + qtdeStr + "0000000000000" + System.getProperty("line.separator")).getBytes());
                            texto = texto + "Produto : " + produto + "\n";
                            texto = texto + "Qtde pedida : " + qtde + " Qtde Faturada : " + Integer.parseInt(linha.substring(15, 19)) + "  \n";
                            texto = texto + "Nova Qtde pedida : " + total + "\n\n";
                        }

                    }


                }
                linha = buffer.readLine();
                if (linha.equals(null)) {
                    i = 1;
                    buffer.close();
                }

            }

        }

        Connection conDistr = Conexao.getConnetion();
        PreparedStatement pstDistr = conDistr.prepareStatement("Select "
                + "pedido.*,distribuidora.descricao,distribuidora.cnpj from pedido "
                + "left outer join distribuidora on pedido.id_distribuidora = distribuidora.id_distribuidora "
                + "where pedido.id_pedido ='" + idPedido + "'");
        ResultSet rsDistr = pstDistr.executeQuery();
        String idDistr = null;
        String nomeDistr = null;
        String cnpjDistr = null;
        while (rsDistr.next()) {
            idDistr = rsDistr.getString("id_distribuidora");
            nomeDistr = rsDistr.getString("descricao");
            cnpjDistr = rsDistr.getString("cnpj");
        }
        Update up = new Update();
        up.setUpdateControle("update pedido set id_distribuidora = '" + idDistrRet + "',num_pedido = '" + pedidoPararetorno + "',nome_arquivo = '" + novoArq + "'  "
                + "where id_pedido = '" + idPedido + "'");
        //  texto = texto + "Distribuidora : " + nomeDistr + "\nCNPJ : " + cnpjDistr+"\n";
        texto = texto + "--------------------------------------------------------------------------------------------"
                + "--------------------------------------------------\n\n";
        return texto;
    }

    public boolean verificaRetorno(String nomeArquivo, String caminhoArquivoEnv) throws SQLException, IOException {
        int posicao;
        String arquivoSemExt;
        posicao = nomeArquivo.indexOf(".");
        boolean isNaoFaturado = false;
        String[] arrayCodigoBarras;
        String[] arrayQtde;

        //Apenas o nome do arquivo sem extensao
        arquivoSemExt = nomeArquivo.substring(0, posicao);

        String codigoBarras = "";
        String qtde = "";
        String produto = "";

        String codigoBarrasRet = "";
        String qtdeRet = "";

        Connection con = Conexao.getConnetion();


        FileReader arquivoCaminho = null;
        File cam = null;

        cam = new File(caminhoArquivoEnv + "\\" + nomeArquivo);
        arquivoCaminho = new FileReader(cam);

        int i = 0;


        BufferedReader buffer = new BufferedReader(arquivoCaminho);
        String linha = buffer.readLine();
        i = 0;
        while (i == 0) {


            if (linha.substring(0, 2).equals("07")) {
                i = 1;
                buffer.close();
            } else {



                System.out.println(linha.substring(0, 2));
                if (linha.substring(0, 2).equals("06")) {


                    System.out.println(linha.substring(2, 15));
                    PreparedStatement pstSelectPedido = con.prepareStatement(
                            "Select pedido.*,pedido_item.* from pedido "
                            + "left outer join pedido_item on pedido.id_pedido = pedido_item.id_pedido "
                            + "where pedido.nome_arquivo = '" + arquivoSemExt + "' and pedido_item.codigo_barras = '" + linha.substring(2, 15) + "'");

                    ResultSet rsSelectPedido = pstSelectPedido.executeQuery();
                    while (rsSelectPedido.next()) {
                        codigoBarras = rsSelectPedido.getString("codigo_barras");
                        qtde = rsSelectPedido.getString("quantidade");
                        produto = rsSelectPedido.getString("produto");

                        System.out.println(linha.substring(15, 19));
                        int total = (Integer.parseInt(qtde) - Integer.parseInt(linha.substring(15, 19)));


                        if (total > 0) {

                            isNaoFaturado = true;


                        }

                    }

                }
                linha = buffer.readLine();
                if (linha.equals(null)) {
                    i = 1;
                    buffer.close();
                }

            }

        }




        return isNaoFaturado;

    }

    public static String getImp2() {
        String result = "";
        try {
            File file = File.createTempFile("tmp", ".vbs");
            file.deleteOnExit();
            FileWriter fw = new java.io.FileWriter(file);

            String vbs =
                    "On Error Resume Next \r\n\r\n"
                    + "strComputer = \".\"  \r\n"
                    + "Set objWMIService = GetObject(\"winmgmts:\" _ \r\n"
                    + "    & \"{impersonationLevel=impersonate}!\\\\\" & strComputer & \"\\root\\cimv2\") \r\n"
                    + "Set colItems = objWMIService.ExecQuery(\"Select * from Win32_Processor\")  \r\n "
                    + "For Each objItem in colItems\r\n "
                    + "    Wscript.Echo objItem.ProcessorId  \r\n "
                    + "    exit for  ' do the first cpu only! \r\n"
                    + "Next                    ";


            fw.write(vbs);
            fw.close();
            Process p = Runtime.getRuntime().exec("cscript //NoLogo " + file.getPath());
            BufferedReader input =
                    new BufferedReader(new InputStreamReader(p.getInputStream()));
            String line;
            while ((line = input.readLine()) != null) {
                result += line;
            }
            input.close();
        } catch (Exception e) {
        }
        if (result.trim().length() < 1 || result == null) {
            result = "NO_CPU_ID";
        }
        return result.trim();
    }

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(Principal.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(Principal.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(Principal.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(Principal.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                try {
                    UIManager.setLookAndFeel("com.jtattoo.plaf.mint.MintLookAndFeel");
                } catch (ClassNotFoundException ex) {
                    Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
                } catch (InstantiationException ex) {
                    Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
                } catch (IllegalAccessException ex) {
                    Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
                } catch (UnsupportedLookAndFeelException ex) {
                    Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
                }
                Principal p = new Principal();
                p.setLocationRelativeTo(null);

                Connection conTranca = Conexao.getConnetion();
                if (getImp2().equals("BFEBFBFF000306A9")) {//note
            //          if (getImp2().equals("AFEBFBFF00010661")) {
                    try {
                        PreparedStatement pstTranca = conTranca.prepareStatement("select * from parame");
                        ResultSet rsTranca = pstTranca.executeQuery();

                        while (rsTranca.next()) {
                            Date data = new Date(System.currentTimeMillis());
                            SimpleDateFormat formatador = new SimpleDateFormat("yyyyMMdd");
                            String dataFormatada = formatador.format(data);
                            Cript cpt = new Cript();
                            String dtNowCript = cpt.SetCript(dataFormatada);

                            String dtBdCript = rsTranca.getString("imp1");
                            String dtBdDecript = cpt.GetDecript(dtBdCript);

                            if (dtBdDecript.length() == 8) {
                                if (Integer.parseInt(dtBdDecript) >= Integer.parseInt(dataFormatada)) {
                                    String a = cpt.GetDecript(rsTranca.getString("imp2"));
                                    if (Integer.parseInt(dataFormatada) >= Integer.parseInt(a)) {

                                        if (cpt.GetDecript(rsTranca.getString("imp2")).length() == 8) {
                                            Connection conG = Conexao.getConnetion();
                                            PreparedStatement pstG = conG.prepareStatement("update parame set imp2 = '" + dtNowCript + "'");
                                            pstG.executeUpdate();

                                            p.setVisible(true);
                                        } else {
                                            JOptionPane.showMessageDialog(null, "Erro na abertura do sistema. Informe o codigo 8775");

                                        }
                                    } else {
                                        JOptionPane.showMessageDialog(null, "Erro na abertura do sistema. Informe o codigo 8777");
                                    }
                                } else {
                                    JOptionPane.showMessageDialog(null, "Erro na abertura do sistema. Informe o codigo 8708");
                                }
                            } else {
                                JOptionPane.showMessageDialog(null, "Erro na abertura do sistema. Informe o codigo 8708");
                            }
                        }
                    } catch (SQLException ex) {
                        Logger.getLogger(Principal.class.getName()).log(Level.SEVERE, null, ex);
                    }

                } else {
                    JOptionPane.showMessageDialog(null, "Erro na abertura do sistema. Informe o codigo 8778");
                }
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton10;
    private javax.swing.JButton jButton11;
    private javax.swing.JButton jButton2;
    private javax.swing.JButton jButton3;
    private javax.swing.JButton jButton4;
    private javax.swing.JButton jButton5;
    private javax.swing.JButton jButton6;
    private javax.swing.JButton jButton7;
    private javax.swing.JButton jButton8;
    private javax.swing.JButton jButton9;
    private javax.swing.JButton jButtonBuscar;
    private javax.swing.JButton jButtonEditar;
    private javax.swing.JButton jButtonExcluir;
    private javax.swing.JButton jButtonFormRetornoAdicionar;
    private javax.swing.JComboBox jComboBoxDistrRetorno;
    private javax.swing.JComboBox jComboBoxDistribuidora;
    private javax.swing.JComboBox jComboBoxProjeto;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel10;
    private javax.swing.JLabel jLabel11;
    private javax.swing.JLabel jLabel12;
    private javax.swing.JLabel jLabel13;
    private javax.swing.JLabel jLabel14;
    private javax.swing.JLabel jLabel15;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JLabel jLabel8;
    private javax.swing.JLabel jLabel9;
    private javax.swing.JLabel jLabelCnpj;
    private javax.swing.JList jList1;
    private javax.swing.JList jList2;
    private javax.swing.JMenuBar jMenuBar1;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel10;
    private javax.swing.JPanel jPanel11;
    private javax.swing.JPanel jPanel12;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JPanel jPanel3;
    private javax.swing.JPanel jPanel4;
    private javax.swing.JPanel jPanel5;
    private javax.swing.JPanel jPanel6;
    private javax.swing.JPanel jPanel7;
    private javax.swing.JPanel jPanel8;
    private javax.swing.JPanel jPanel9;
    private javax.swing.JProgressBar jProgressBar1;
    private javax.swing.JProgressBar jProgressBar2;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JScrollPane jScrollPane3;
    private javax.swing.JScrollPane jScrollPane4;
    private javax.swing.JScrollPane jScrollPane5;
    private javax.swing.JScrollPane jScrollPane6;
    private javax.swing.JTabbedPane jTabbedPane1;
    private javax.swing.JTable jTable1;
    private javax.swing.JTextArea jTextArea1;
    private javax.swing.JTextArea jTextArea2;
    private javax.swing.JTextArea jTextArea4;
    private javax.swing.JTextField jTextField1;
    private javax.swing.JTextField jTextFieldBusca;
    private javax.swing.JTextField jTextFieldCaminhoEnvio;
    private javax.swing.JTextField jTextFieldCaminhoPlanilha;
    private javax.swing.JTextField jTextFieldCaminhoRetorno;
    private javax.swing.JTextField jTextFieldCnpj;
    private javax.swing.JTextField jTextFieldDescricao;
    private javax.swing.JTextField jTextFieldDistrOrigem;
    // End of variables declaration//GEN-END:variables
}
